﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MapgisEbp.QuickDataBase;
using Logistics.DataManage.Local;
using Logistics.DataManage.DAL;
using System.Data;
using Logistics.Utils;

namespace Logistics.DataManage
{
    class WaybillDAO
    {
        public static bool IsBillExist(String BillId)
        {
            String sql = "select count(WaybillID) from WaybillInfo where WaybillID=?";
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, BillId);
            Object obj = LocalDb.Handler.ExecuteScalar(PSQL.SQL, PSQL.Parameters);
            return Convert.ToInt32(obj) > 0;
        }

        public static OWaybill GetBill(String BillId)
        {
            DataSet ds = GetBillSetById(BillId);
            IList<OWaybill> list = DataConvert.ConvertToEntity<OWaybill>(ds);
            return list.Count>0? list[0]:null;
        }
        public static DataSet GetBillSetById(String BillId)
        {
            String sql = "select * from WaybillInfo where WaybillID=?";
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, BillId);
            DataSet ds = LocalDb.Handler.ExecuteQuery(PSQL.SQL, PSQL.Parameters);
            return ds;
        }

        public static bool AddBill(OWaybill Bill)
        {
            DataTable dt = LocalDb.TableTemplates.WaybillInfo; 
            List<String> fieldNames = new List<string>();
            foreach (DataColumn col in dt.Columns)
            {
                fieldNames.Add(col.ColumnName);
            }
            IList<Object> Parameters=null;
            String insertSQL = DataConvert.GenerateInsertSQL(Bill, "WaybillInfo", fieldNames.ToArray<String>(), out Parameters);
            if (!String.IsNullOrEmpty(insertSQL))
            {
                ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(insertSQL, Parameters.ToArray());
                int result = LocalDb.Handler.ExecuteNonQuery(PSQL.SQL, PSQL.Parameters);
                return result == 1;
            }
            return false;
        }

        public static bool UpdateBill(OWaybill Bill)
        {
            DataTable dt = LocalDb.TableTemplates.WaybillInfo;
            List<String> fieldNames = new List<string>();
            foreach (DataColumn col in dt.Columns)
            {
                fieldNames.Add(col.ColumnName);
            }
            IList<Object> Parameters = null;
            String updateSQL = DataConvert.GenerateUpdateSQL(Bill, "WaybillInfo", fieldNames.ToArray<String>(), "WaybillID", Bill.WaybillID, out Parameters);
            if (!String.IsNullOrEmpty(updateSQL))
            {
                ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(updateSQL, Parameters.ToArray());
                int result = LocalDb.Handler.ExecuteNonQuery(PSQL.SQL, PSQL.Parameters);
                return result == 1;
            }
            return false;
        }

        /// <summary>
        /// 得到当天的所有订单数目
        /// </summary>
        /// <returns></returns>
        public static int GetCurrentBillCount()
        {
            DateTime Today = DateTime.Today;
            String sql = "select count(WaybillID) from WaybillInfo where CreatedDate>?";
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, Today);
            Object count = LocalDb.Handler.ExecuteScalar(PSQL.SQL, PSQL.Parameters);
            return Convert.ToInt32(count) ;
        }
        /// <summary>
        /// 得到所有订单数目
        /// </summary>
        /// <returns></returns>
        public static int GetAllBillCount()
        {
            DateTime Today = DateTime.Today;
            String sql = "select count(WaybillID) from WaybillInfo ";
            Object count = LocalDb.Handler.ExecuteScalar(sql);
            return Convert.ToInt32(count);
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="BillId"></param>
        public static void DeleteWayBill(String BillId)
        {
            String sql = "delete from WaybillInfo where WaybillID=?";
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, BillId);
            LocalDb.Handler.ExecuteNonQuery(PSQL.SQL, PSQL.Parameters);
        }

        /// <summary>
        /// 获取站点间的货物订单数量
        /// </summary>
        /// <param name="s_StiteID">始发站编号</param>
        /// <param name="e_SiteID">目的站的编号</param>
        /// <param name="exceptId">排除ID</param>
        /// <returns></returns>
        public static int GetBillCountBySite(string s_StiteID, string e_SiteID, String exceptId)
        {
            string sql = "select count(WaybillID) from  WaybillInfo where Destinations=? and Origin=? and WaybillID<>?";
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, e_SiteID, s_StiteID, exceptId);
            Object count = LocalDb.Handler.ExecuteScalar(PSQL.SQL, PSQL.Parameters);
            return Convert.ToInt32(count);
        }

        #region 列表数据查询
        public static DataSet QueryUserBills(String userName, DateTime? startDt, DateTime? endDt,int pageSize,int pageIndex)
        {
            int offset = pageSize * pageIndex;
            String pager = "limit "+offset+","+pageSize;
            IDataParameter[] paras;
            String condition = GenerateWhere(userName, startDt, endDt, out paras);
            String sql = "select * from WaybillInfo where " + condition + pager;
            DataSet ds = LocalDb.Handler.ExecuteQuery(sql, paras);
            return ds;
        }
        public static int GetUserBillsCount(String userName, DateTime? startDt, DateTime? endDt)
        {
            IDataParameter[] paras;
            String condition = GenerateWhere(userName, startDt, endDt, out paras);
            String sql = "select count(WaybillID) from WaybillInfo where " + condition;
            Object count = LocalDb.Handler.ExecuteScalar(sql, paras);
            return Convert.ToInt32(count);
        }

        private static String GenerateWhere(String userName, DateTime? startDt, DateTime? endDt, out IDataParameter[] paras)
        {
            List<Object> lparas = new List<object>();
            String sql = " Column5 is null ";//Column5是否已上传
            if (!String.IsNullOrEmpty(userName))
            {
                sql += " and CreateUser=?";
                lparas.Add(userName);
            }
            if (startDt != null)
            {
                sql += " and CreatedDate > ?";
                lparas.Add(startDt.Value);
            }
            if (endDt != null)
            {
                sql += " and CreatedDate < ?";
                lparas.Add(endDt.Value);
            }
            ParameterizedSQL PSQL = LocalDb.Handler.DbAdapter.AdaptSQLAnonymousParams(sql, lparas.ToArray());
            paras = PSQL.Parameters;
            return PSQL.SQL;
        }
        #endregion
    }
}
